package com.testDemo.datasource;

import cn.hutool.db.ds.DSFactory;

import javax.sql.DataSource;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * @author: zouren
 * @date: 2023/6/21
 * @description:
 */
public class OracleTest {


    private Connection createConnection() throws SQLException {
        DataSource ds = DSFactory.get();
        return ds.getConnection();
    }
    private void closeConnection(Connection con, ResultSet res) {
        try {
            if(con!=null){
                con.close();
            }
           if(res!=null){
               res.close();
           }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }

    /**
     *
     *  执行过程语句
     */
    private void executeCallable(String _sql, CallableStatement _call,
                                 Connection _con) throws SQLException {
        _call = _con.prepareCall(_sql);
        _call.execute();
    }

    /**

     *该方法是获取oracle日志地址,封装到一个list中

     */

    private List<String> doLogAddress() throws SQLException {
        List<String> listLog = new ArrayList<String>();

//数据库连接
        Connection con = this.createConnection();
        Statement stat = con.createStatement();
// 获取日志地址
        String logSql = "select group#,member from v$logfile ORDER BY group#";
        ResultSet res = null;
        try {
            res = stat.executeQuery(logSql);
            while (res.next()) {
                listLog.add(res.getString(2));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            this.closeConnection(con, res);
        }
        return listLog;
    }



    /**

     *添加日志文件返回一个String

     */

    private String doAddfileSQL(List<String> _listLog) {
        StringBuffer addLogSql = new StringBuffer();
        addLogSql.append("BEGIN ");
        addLogSql.append("dbms_logmnr.add_logfile(logfilename=>'"
                + _listLog.get(0).replaceAll("\\\\", "\\\\\\\\")
                + "',options=>dbms_logmnr.NEW);");
        for (int i = 1; i < _listLog.size() - 1; i++) {
            addLogSql.append("dbms_logmnr.add_logfile(logfilename=>'"
                    + _listLog.get(i).replaceAll("\\\\", "\\\\\\\\")
                    + "',options=>dbms_logmnr.addfile);");
        }
        addLogSql.append("END;");
        return addLogSql.toString();
    }

    /**

     *获取指定日期后的更新和插入数据的rowid

     *_date是指定日期,格式:yyyy-MM-dd HH24:mi:ss
     *tableName 是表名

     */

    public List<String> doUpdateAndInsertToData(String _date,String tableName)throws SQLException {
        Connection con = this.createConnection();
        Statement stat = null;
        CallableStatement callableStatement = null;
        List<String> listLog = new ArrayList<String>();
        try {
            listLog = this.doLogAddress();
            stat = con.createStatement();
// 添加所有日志
            String addLogSQL = this.doAddfileSQL(listLog);
            this.executeCallable(addLogSQL, callableStatement, con);
            System.out.println("添加日志完成");
// 开始分析日志,联网方式
            String startLog = "BEGIN dbms_logmnr.start_logmnr(OPTIONS=>DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG);END;";
            this.executeCallable(startLog, callableStatement, con);
            System.out.println("日志分析完成");
// 查询日志内容,将更新和插入操作的数据全部查出来
            String analysisLog = "SELECT t.ROW_ID FROM v$logmnr_contents t where t.TABLE_NAME='"+tableName+"' "
                    + "and (t.OPERATION like 'UPDATE' or t.OPERATION like 'INSERT') "
                    + "and to_date(TO_CHAR(SCN_TO_TIMESTAMP(t.scn), 'yyyy-MM-dd HH24:mi:ss'),'yyyy-MM-dd HH24:mi:ss')"
                    + ">to_date('" + _date + "','yyyy-MM-dd HH24:mi:ss')";
            ResultSet result = stat.executeQuery(analysisLog);
            listLog.clear();
            while (result.next()) {
                System.out.println(result.getString(1));
                listLog.add(result.getString(1));
            }
// 分析完成后,释放内存
            String endLogSQL = "BEGIN dbms_logmnr.end_logmnr;END;";
            this.executeCallable(endLogSQL, callableStatement, con);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return listLog;
    }

    public static void main(String[] args)throws Exception {
        OracleTest oracleTest = new OracleTest();

        oracleTest.doUpdateAndInsertToData("yyyy-MM-dd HH24:mi:ss","test");
    }

}
